package com.hcj.util.excelUtil;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import javax.swing.JOptionPane;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil<T> {
/**
 * 导入
 * @param c
 * @param in 
 * @param map:key为excel表格对应列的字段名,value为excel表格对应列的列号
 * @return
 * @throws IOException
 * @throws IllegalArgumentException
 * @throws IllegalAccessException
 * @throws NoSuchMethodException
 * @throws SecurityException
 * @throws InstantiationException
 * @throws InvocationTargetException
 * 
 */
@SuppressWarnings({ "rawtypes", "unchecked", "null", "deprecation" })
public  List<T> getDataFromExcel(Class c,  InputStream in,HashMap<String,Integer> map) throws IOException, IllegalArgumentException, IllegalAccessException, NoSuchMethodException, SecurityException, InstantiationException, InvocationTargetException{

       @SuppressWarnings("resource")
	Workbook wookbook =new XSSFWorkbook(in);//得到工作簿
       Sheet sheet = wookbook.getSheetAt(0);  //得到一个工作表
       List<T> listEntity= new ArrayList<T>();
       Field[] fields=c.getDeclaredFields();
       int steetsize=  sheet.getLastRowNum();
    	   for(int i=1;i<=steetsize;i++){
    		
    		   Constructor c0=c.getDeclaredConstructor();         
               c0.setAccessible(true);  
               T  t=(T)c0.newInstance();   
               Row row = sheet.getRow(i);
            if(row!=null){
            	for(Cell cell:row){
            		if(cell==null){
                        cell.setCellValue("");
            	      	}
                        for(Field field:fields){
                        	  field.setAccessible(true);
                			  String fname=field.getName();
                			String type=field.getType().toString();
            				  for (String key : map.keySet()) {
        					  if(fname.equals(key)&&cell.getColumnIndex()==map.get(key)){
        						  if(type.endsWith("String")){
        							  cell.setCellType(Cell.CELL_TYPE_STRING); 
        							  String fvalue=cell.getStringCellValue().toString();        							
        							  field.set(t,fvalue) ; 
        						  }else if(type.endsWith("int") || type.endsWith("Integer")){ 
        							  System.out.println(  fname+"::::::"+cell);
        							  Integer fvalue=(int)cell.getNumericCellValue();
        				             field.set(t, fvalue);
        				           }else if(type.endsWith("Long")){
        				        	   Long fvalue=(long) cell.getNumericCellValue();   				        	
        				        	   field.set(t, fvalue);
        				           }else if(type.endsWith("Double")){
        				        	   double fvalue=(double) cell.getNumericCellValue();
        				        	   field.set(t, fvalue);
        				           }else if(type.endsWith("Date")){
        				        	   Date fvalue=cell.getDateCellValue();
        				        	   field.set(t, fvalue);
        				           } else{  
        				        	 System.out.println(field.getType()+"\t");  
        				           }
        						    
        					   }
        					  }
                         }
            		
            	}
            		
    	   }
               listEntity.add(t);
    	   }
   		
	return listEntity;
}
/**
 * 导出
 * @param list
 * @param map
 * @throws IllegalArgumentException
 * @throws IllegalAccessException
 * @throws IOException
 */
@SuppressWarnings({ "null", "resource" })
public  void export(List<T> list,HashMap<String,Short> map) throws IllegalArgumentException, IllegalAccessException, IOException{
	XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet("sheet1");
    sheet.setDefaultColumnWidth((short)15);
    XSSFCellStyle style = wb.createCellStyle();
    XSSFRow row = sheet.createRow(0);
    //生成表头
	  for (String key : map.keySet()) {
		  XSSFCell cell = row.createCell(map.get(key));
		   cell.setCellValue(key); 
		   cell.setCellStyle(style);
	  }
  //生成数据
	  T t=null;
	  Field f=null;
	  Field[] fields = t.getClass().getDeclaredFields();
	  for(int i=0;i<list.size();i++){
		  t=list.get(i);
	      row = sheet.createRow(i + 1);
	      for (String key : map.keySet()) {
	    	     for(int j=0;j<fields.length;j++){
			    	 f=fields[j];
			    	 f.setAccessible(true);			    	
			    	  if(f.getName().equals(key)){
			    		  if(f.get(t)!=null||!"".equals(f.get(t))){
			    			  row.createCell(map.get(key)).setCellValue(f.get(t).toString());
				    	  }else{
				    		  row.createCell(map.get(key)).setCellValue("");
				    	  }
			    	  }
			     }
	      }   
       }
	  FileOutputStream out = new FileOutputStream("E://导出.xlsx");
      wb.write(out);
      out.close();
      JOptionPane.showMessageDialog(null, "导出成功!");
    }
}
